package com.bookmanger.server.mapper;

import com.bookmanger.pojo.DTO.BorrowDTO;
import com.bookmanger.pojo.Entity.BorrowEntity;
import com.bookmanger.pojo.VO.BorrowBook4ManagerVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface BorrowMapper {

    /**
     * 插入借阅记录
     *
     * @param borrowDTO 借阅信息
     * @return 影响的行数
     */
    @Insert("INSERT INTO borrow(reader_id, book_id, isbn_id, borrow_time, due_time) VALUES(#{readerId}, #{bookId}, #{isbnId}, #{borrowTime}, #{dueTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int insertBorrowRecord(BorrowDTO borrowDTO);

    /**
     * 根据ID查找借阅记录
     *
     * @param id 借阅记录ID
     * @return 借阅实体
     */
    @Select("SELECT * FROM borrow WHERE id = #{id}")
    BorrowEntity findById(Integer id);

    /**
     * 更新借阅记录的归还时间
     *
     * @param id 借阅记录ID
     * @return 影响的行数
     */
    @Update("UPDATE borrow SET return_time = NOW() WHERE id = #{id}")
    int updateReturnTime(Integer id);

    /**
     * 标记借阅记录为已处理（用于图书损坏或丢失）
     *
     * @param id 借阅记录ID
     * @return 影响的行数
     */
    @Update("UPDATE borrow SET return_time = NOW() WHERE id = #{id}")
    int markAsHandled(Integer id);

    /**
     * 更新借阅记录的借阅管理员ID
     *
     * @param id        借阅记录ID
     * @param managerId 管理员ID
     * @return 影响的行数
     */
    @Update("UPDATE borrow SET borrow_manager_id = #{managerId} ,book_id = #{bookId} WHERE id = #{id}")
    int updateBorrowManagerId(@Param("id") Integer id, @Param("managerId") Integer managerId, @Param("bookId") Integer bookId);

    /**
     * 更新借阅记录的归还管理员ID
     *
     * @param id        借阅记录ID
     * @param managerId 管理员ID
     * @return 影响的行数
     */
    @Update("UPDATE borrow SET return_manager_id = #{managerId} WHERE id = #{id}")
    int updateReturnManagerId(@Param("id") Integer id, @Param("managerId") Integer managerId);

    /**
     * 查找待处理的借阅申请
     *
     * @return 借阅实体列表
     */
    @Select("SELECT * FROM borrow WHERE borrow_manager_id IS NULL")
    List<BorrowEntity> findPendingBorrowApplications();

    /**
     * 查找已处理的借阅申请
     *
     * @return 借阅实体列表
     */
    @Select("SELECT * FROM borrow WHERE borrow_manager_id IS NOT NULL")
    List<BorrowEntity> findApprovedBorrowApplications();

    /**
     * 查找待处理的归还申请
     *
     * @return 借阅实体列表
     */
    @Select("SELECT * FROM borrow WHERE return_time IS NOT NULL AND return_manager_id IS NULL")
    List<BorrowEntity> findPendingReturnApplications();

    /**
     * 查找已处理的归还申请
     *
     * @return 借阅实体列表
     */
    @Select("SELECT * FROM borrow WHERE return_manager_id IS NOT NULL")
    List<BorrowEntity> findApprovedReturnApplications();

    /**
     * 查找待处理的借阅申请(详细信息)
     *
     * @return BorrowBook4ManagerVO列表
     */
    @Select("SELECT b.id, b.reader_id, b.book_id, b.isbn_id, b.borrow_time, b.due_time, b.return_time, b.borrow_manager_id, b.return_manager_id, " +
            "r.reader_number, r.name AS reader_name, " +
            "i.title, i.author, i.publisher, i.price, i.isbn " +
            "FROM borrow b " +
            "JOIN reader r ON b.reader_id = r.id " +
            "JOIN isbn_tb i ON b.isbn_id = i.id " +
            "WHERE b.borrow_manager_id IS NULL " +
            "ORDER BY b.borrow_time ASC")
    List<BorrowBook4ManagerVO> findPendingBorrowApplicationsWithDetails();

    /**
     * 查找已处理的借阅申请(详细信息)
     *
     * @return BorrowBook4ManagerVO列表
     */
    @Select("SELECT b.id, b.reader_id, b.book_id, b.isbn_id, b.borrow_time, b.due_time, b.return_time, b.borrow_manager_id, b.return_manager_id, " +
            "r.reader_number, r.name AS reader_name, " +
            "bk.book_number, " +
            "i.title, i.author, i.publisher, i.price, i.isbn " +
            "FROM borrow b " +
            "JOIN reader r ON b.reader_id = r.id " +
            "JOIN book bk ON b.book_id = bk.id " +
            "JOIN isbn_tb i ON b.isbn_id = i.id " +
            "WHERE b.borrow_manager_id IS NOT NULL " +
            "ORDER BY b.borrow_time DESC")
    List<BorrowBook4ManagerVO> findApprovedBorrowApplicationsWithDetails();

    /**
     * 查找待处理的归还申请(详细信息)
     *
     * @return BorrowBook4ManagerVO列表
     */
    @Select("SELECT b.id, b.reader_id, b.book_id, b.isbn_id, b.borrow_time, b.due_time, b.return_time, b.borrow_manager_id, b.return_manager_id, " +
            "r.reader_number, r.name AS reader_name, " +
            "bk.book_number, " +
            "i.title, i.author, i.publisher, i.price, i.isbn " +
            "FROM borrow b " +
            "JOIN reader r ON b.reader_id = r.id " +
            "JOIN book bk ON b.book_id = bk.id " +
            "JOIN isbn_tb i ON b.isbn_id = i.id " +
            "WHERE b.return_time IS NULL AND b.return_manager_id IS NULL " +
            "ORDER BY b.return_time ASC")
    List<BorrowBook4ManagerVO> findPendingReturnApplicationsWithDetails();

    /**
     * 查找已处理的归还申请(详细信息)
     *
     * @return BorrowBook4ManagerVO列表
     */
    @Select("SELECT b.id, b.reader_id, b.book_id, b.isbn_id, b.borrow_time, b.due_time, b.return_time, b.borrow_manager_id, b.return_manager_id, " +
            "r.reader_number, r.name AS reader_name, " +
            "bk.book_number, " +
            "i.title, i.author, i.publisher, i.price, i.isbn " +
            "FROM borrow b " +
            "JOIN reader r ON b.reader_id = r.id " +
            "JOIN book bk ON b.book_id = bk.id " +
            "JOIN isbn_tb i ON b.isbn_id = i.id " +
            "WHERE b.return_manager_id IS NOT NULL " +
            "ORDER BY b.return_time DESC")
    List<BorrowBook4ManagerVO> findApprovedReturnApplicationsWithDetails();

    /**
     * 根据用户ID或名称模糊查询借阅记录
     *
     * @param keyword 关键词
     * @return BorrowBook4ManagerVO列表
     */
    @Select("SELECT b.id, b.reader_id, b.book_id, b.isbn_id, b.borrow_time, b.due_time, b.return_time, b.borrow_manager_id, b.return_manager_id, " +
            "r.reader_number, r.name AS reader_name, " +
            "bk.book_number, " +
            "i.title, i.author, i.publisher, i.price, i.isbn " +
            "FROM borrow b " +
            "JOIN reader r ON b.reader_id = r.id " +
            "JOIN book bk ON b.book_id = bk.id " +
            "JOIN isbn_tb i ON b.isbn_id = i.id " +
            "WHERE r.reader_number LIKE CONCAT('%', #{keyword}, '%') OR r.name LIKE CONCAT('%', #{keyword}, '%') " +
            "ORDER BY b.borrow_time DESC")
    List<BorrowBook4ManagerVO> findBorrowRecordsByUserIdOrName(@Param("keyword") String keyword);
}